Create Staging Table in Staging Database and Populate the Staging Tables 9
Download the Sql
File attached to the
Run
Script that is attached to create your
Staging tables in the Staging Database. Once ran the tables will be made up of
these columns.
*Using SSIS Toolbox, the following components
will be drag in the Control Flow Dashboard;
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
string strFullFileName; string strFileName;
strFullFileName =
Dts.Variables["User::JSONFilePath"].Value.ToString();
strFileName =
Path.GetFileName(strFullFileName);
Dts.Variables["User::JSONFileName"].Value = strFileName;
Dts.TaskResult =
(int)ScriptResults.Success;
C# Code for Class
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace WildLifenamespace
{
class WildLife
{
public int INDEX_NR { get; set; }
public String INCIDENT_DATE { get; set; }
public int INCIDENT_MONTH { get; set; }
public int INCIDENT_YEAR { get; set; }
public String TIME_OF_DAY { get; set; }
public String AIRPORT_ID { get; set; }
public String AIRPORT { get; set; }
public String STATE { get; set; }
public String FAAREGION { get; set; }
public String OPID { get; set; }
public String OPERATOR { get; set; }
public String REG { get; set; }
public String FLT { get; set; }
public String AIRCRAFT { get; set; }
public String AMA { get; set; }
public String AMO { get; set; }
public String EMA { get; set; }
public String EMO { get; set; }
public String AC_CLASS { get; set; }
public String AC_MASS { get; set; }
public String TYPE_ENG { get; set; }
public String NUM_ENGS { get; set; }
public String PHASE_OF_FLIGHT { get; set; }
public String HEIGHT { get; set; }
public String SPEED { get; set; }
public String SKY { get; set; }
public String AOS { get; set; }
public String COST_REPAIRS { get; set; }
public String OTHER_COST { get; set; }
public String INDICATED_DAMAGE { get; set; }
public String EFFECT { get; set; }
public String SPECIES_ID { get; set; }
public String SPECIES { get; set; }
public String WARNED { get; set; }
public String DAMAGE_LEVEL { get; set; }
public String NR_FATALITIES { get; set; }
public String NR_INJURIES { get; set; }
} }
?C#
Code for Main
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Text;
using System.Web.Script.Serialization;
using System.IO;
using WildLifenamespace;
#endregion
String readfile = Variables.JSONFilePath;
String jsonFileContent =
File.ReadAllText(readfile);
JavaScriptSerializer js = new
JavaScriptSerializer(){ MaxJsonLength = 86753090 };
List<WildLife> ReportData =
js.Deserialize<List<WildLife>>(jsonFileContent);
foreach (WildLife Items in ReportData)
{
Output0Buffer.AddRow();
Output0Buffer.INDEXNR =
Items.INDEX_NR;
Output0Buffer.INCIDENTDATE = Items.INCIDENT_DATE;
Output0Buffer.INCIDENTMONTH =
Items.INCIDENT_MONTH;
Output0Buffer.INCIDENTYEAR =
Items.INCIDENT_YEAR;
Output0Buffer.TIMEOFDAY =
Items.TIME_OF_DAY;
Output0Buffer.AIRPORTID =
Items.AIRPORT_ID;
Output0Buffer.AIRPORT =
Items.AIRPORT;
Output0Buffer.STATE = Items.STATE;
Output0Buffer.FAAREGION =
Items.FAAREGION;
Output0Buffer.OPID = Items.OPID;
Output0Buffer.OPERATOR =
Items.OPERATOR;
Output0Buffer.REG = Items.REG;
Output0Buffer.FLT = Items.FLT;
Output0Buffer.AIRCRAFT =
Items.AIRCRAFT;
Output0Buffer.AMA = Items.AMA;
Output0Buffer.AMO = Items.AMO;
Output0Buffer.EMA = Items.EMA;
Output0Buffer.EMO = Items.EMO;
Output0Buffer.ACCLASS =
Items.AC_CLASS;
Output0Buffer.ACMASS = Items.AC_MASS;
Output0Buffer.TYPEENG =
Items.TYPE_ENG;
Output0Buffer.NUMENGS =
Items.NUM_ENGS;
Output0Buffer.PHASEOFFLIGHT =
Items.PHASE_OF_FLIGHT;
Output0Buffer.HEIGHT = Items.HEIGHT;
Output0Buffer.SPEED = Items.SPEED;
Output0Buffer.SKY = Items.SKY;
Output0Buffer.AOS = Items.AOS;
Output0Buffer.COSTREPAIRS =
Items.COST_REPAIRS;
Output0Buffer.OTHERCOST =
Items.OTHER_COST;
Output0Buffer.INDICATEDDAMAGE =
Items.INDICATED_DAMAGE;
Output0Buffer.EFFECT = Items.EFFECT;
Output0Buffer.SPECIESID =
Items.SPECIES_ID;
Output0Buffer.SPECIES =
Items.SPECIES;
Output0Buffer.WARNED = Items.WARNED;
Output0Buffer.DAMAGELEVEL =
Items.DAMAGE_LEVEL;
Output0Buffer.NRFATALITIES =
Items.NR_FATALITIES;
Output0Buffer.NRINJURIES =
Items.NR_INJURIES;
}
·
Drag
and drop the Conditional Split - Double click and add condition to remove the
Null values
·
Drag
and Drop the Derived Column transformation - add the new column Filename and
Insert date
·
Drag
and drop the Data Conversion transformation - to convert the appropriate Data
Types
· Drag and drop the OLEDB Destination - add the destination connection manager and select the staging table to load the data